import ast
import sqlite3
import re
import time
import decimal
import numpy as np
# from threading import Thread
import requests
import zlib
import json
from threading import Thread
# 自定义函数
from const import *
import savewarning

# from dealdatatoimage import get_main_image


# 用来检查近三次间隔误差（30分钟）
# 定义检查函数
def check_threshold(differences, base_list):
    results = []
    index = 1
    for (dx, dy), (base_x, base_y) in zip(differences, base_list):
        threshold_x = abs(base_x * 0.3)
        threshold_y = abs(base_y * 0.3)
        is_x_out = abs(dx) > threshold_x
        is_y_out = abs(dy) > threshold_y
        if is_x_out or is_y_out:
            results.append((index, base_x, base_y))
        index += 1
    return results


# 检查差值
def check_res(list1, list2, list3):
    # 计算差值
    diff13 = [(round(a1 - a2, 2), round(b1 - b2, 2))
              for (a1, b1), (a2, b2) in zip(list1, list3)]
    diff23 = [(round(a3 - a2, 2), round(b3 - b2, 2))
              for (a3, b3), (a2, b2) in zip(list2, list3)]
    result1 = check_threshold(diff13, list3)
    result2 = check_threshold(diff23, list3)
    return result1, result2


def save_data1(recvdata, recvtime, station):
    '''
    保存数据到数据库
    '''
    conn = sqlite3.connect(db1_path)
    cur = conn.cursor()
    try:
        if '(' in recvdata:
            recvdata = re.findall(r'\d+\.?\d*', recvdata)
            recvdata.append(
                time.strftime("%Y-%m-%d %H:00:00", time.localtime()))
            conn = sqlite3.connect(db1_path)
            cur = conn.cursor()
            recvtime = time.strftime("%Y-%m-%d %H:00:00", time.localtime())
            #转换成时间数组
            timeArray = time.strptime(recvtime, "%Y-%m-%d %H:%M:%S")
            #转换成时间戳
            timestamp = time.mktime(timeArray)
            for index in range(0, len(recvdata) - 1, 2):
                # print(recvdata[index], recvdata[index + 1], timestamp)
                # print("插入测压管数据")
                sqltxt = f"INSERT INTO 'cyg' (name,shuju,time) VALUES ({recvdata[index]},{recvdata[index+1]},{int(timestamp)})"
                print(sqltxt)
                cur.execute(sqltxt)

            # with open('./data/cyag1.txt', 'a') as f:
            #     f.write(str(recvdata) + '\n')
            # print(recvdata)
        elif '[[' in recvdata:
            '''
            ['JZ1', '-4.03', '168.09', '1826.82', '-536.43', '-0.96', '91.14', '215.9', '136.3', '36.67', '37.0', '67.7', '68.5', '40.0', '60.5', '59.8', '39.0', '39.7', '39.8', '39.9', '66.0', '43.1', '42.0', '35.3', '34.4', '28.8', '102.2', '59.0', '57.0', '60.0', '7.0', '8.0', '19.0', '11.0', '19.0', '48.0', '3.0', '0.0', '6.53', '6.51', '6.52', '167.0', '170.89', '0', '1', '0', '0', '0', '1', '0', '0', '1', '1', '1', '1', '0']
            '''
            recvdata = ast.literal_eval(recvdata)
            recvtime = int(time.time())
            for data in recvdata:
                if 'YX' in data:
                    data.pop(data.index('YX'))
                # recvdata.append(int(time.time()))
                content_len = len(data)
                name = data[0]
                # recvtime = int(time.time())
                db_len = content_len - 1
                db_titles = ','.join(
                    [f'`{str(i + 1)}`' for i in range(db_len)])
                db_contents = ','.join(
                    str(-999) if x == 'None' else str(x) for x in data[1:])
                sql = f'''INSERT INTO `data` (`name`,`time`,{db_titles}) VALUES ('{name}', {recvtime}, {db_contents});'''
                # print(sql)
                cur.execute(sql)
            # 检查振摆是否有报警
            th1 = Thread(target=savewarning.check_zb_warning,
                         args=("振摆", ),
                         daemon=True)
            th2 = Thread(target=savewarning.check_vol_waring,
                         args=("功率", ),
                         daemon=True)
            th1.start()
            th2.start()
        else:
            # with open('./data/lognew1.txt', 'a') as f:
            #     f.write(str(int(recvtime)) + ' ' + recvdata + '\n')
            results = re.findall(r'-{0,1}\d+\.{0,1}\d{0,3}',
                                 recvdata.replace('10m', ''))

            # print(results_length)
            try:
                results_last = int(results[-1])
            except:
                results_last = 0
            # 抽水状态
            if results_last >= 0:
                with open('./data/log_bak1.txt', 'a') as f:
                    f.write(
                        str(int(recvtime)) + ',' + ','.join(results) + '\n')
                results_length = len(results)
                for i in range(5):
                    dydl_byq_new_data = [
                        results[0 + i * 9],
                        results[1 + i * 9],
                        results[2 + i * 9],
                        results[3 + i * 9],
                        results[4 + i * 9],
                        results[5 + i * 9],
                        results[6 + i * 9],
                        results[7 + i * 9],
                        results[8 + i * 9],
                    ]
                    dydl_byq_insert_data = list(map(float, dydl_byq_new_data))

                    sql1 = f'''SELECT "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos" FROM "main"."dianyadianliu" WHERE "ename" LIKE "{byq_list[i]}" ORDER BY "time" DESC LIMIT 1'''
                    cur.execute(sql1)
                    res = cur.execute(sql1)
                    db_res = res.fetchall()
                    if db_res:
                        Uab, Ubc, Uca, Ia, Ib, Ic, P, Q, COS = db_res[0]
                        old_data = [Uab, Ubc, Uca, Ia, Ib, Ic, P, Q, COS]
                        if (np.array([old_data
                                      ]) == np.array([dydl_byq_insert_data
                                                      ])).all():
                            print(f"\033[33m{byq_list[i]}# 电气skip\033[0m")
                        else:
                            # print("insert")
                            dydl_byq_insert_data = list(
                                map(str, dydl_byq_insert_data))
                            sql = f'''INSERT INTO "main"."dianyadianliu" ("ename", "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos","time") VALUES("{byq_list[i]}", {','.join(dydl_byq_insert_data)}, {int(recvtime)})'''
                            # print(sql)
                            print(f"\033[4m{sql}\033[0m")
                            cur.execute(sql)
                conn.commit()
                for jizu_num in range(4):
                    # 电压电流功率cos
                    dydl_new_data = [
                        results[54 + jizu_num * 11],
                        results[55 + jizu_num * 11],
                        results[56 + jizu_num * 11],
                        results[57 + jizu_num * 11],
                        results[58 + jizu_num * 11],
                        results[59 + jizu_num * 11],
                        results[60 + jizu_num * 11],
                        results[61 + jizu_num * 11],
                        results[62 + jizu_num * 11],
                        results[162 + jizu_num * 4],  # lcU
                        results[163 + jizu_num * 4]  #lcI
                    ]
                    sql = f'''INSERT INTO "main"."dianyadianliu" ("ename", "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos","lcU","lcI","time") VALUES({jizu_num + 1}, {','.join(dydl_new_data)}, {int(recvtime)})'''
                    print(f"\033[4m{sql}\033[0m")
                    cur.execute(sql)
                conn.commit()
                # if results_length == 190:
                # 变压器温度
                byq_wendu_list = results[45:54]
                byq_wendu_list.append(str(int(recvtime)))
                sql = 'INSERT INTO byqwd (zbwd,sbA,sbB,sbC,sbD,zbA,zbB,zbC,zbD,time) VALUES(?,?,?,?,?,?,?,?,?,?)'
                cur.execute(sql, byq_wendu_list)
                print(f"\033[4m{byq_wendu_list}\033[0m")
                conn.commit()
                # 供水
                gongshui_new_data = (int(recvtime), results[182], results[183],
                                     results[184], results[185], results[186])
                print(f"\033[4m{gongshui_new_data}\033[0m")
                sql = '''INSERT INTO gongshui (time,mgyl,mgwd,cswd1,cswd2,cswd3) VALUES (?,?,?,?,?,?)'''
                cur.execute(sql, gongshui_new_data)
                conn.commit()
                # 温度
                for jizu_num in range(4):
                    sql = f'''INSERT INTO "main"."wendu" ("ename", "t_U2", "t_U3", "t_V3", "t_W1", "t_W2", "t_W3", "t_Z5", "t_Z6", "t_Z7", "t_Z9", "t_X1", "t_X2", "t_Z10", "t_Z11", "t_syg", "t_xyg", "time")  VALUES({jizu_num +1}, {','.join(results[98+jizu_num*16:98+(jizu_num+1)*16])}, {int(recvtime)})'''
                    print(f"\033[4m{sql}\033[0m")
                    cur.execute(sql)
                    # # 转速
                    # zs_new_data = [results[165 + jizu_num * 4]]
                    # zs_insert_data = list(map(float, zs_new_data))
                    # # print(jizu_num + 1, "#", zs_insert_data)
                    # sql1 = f'''SELECT "zhuansu" FROM "main"."zhuansu" WHERE "ename" LIKE "{jizu_num+1}" ORDER BY "time" DESC LIMIT 1'''
                    # # print(sql1)
                    # cur.execute(sql1)
                    # res = cur.execute(sql1)
                    # db_res = res.fetchall()
                    # if db_res:
                    #     zhuansu = db_res[0]
                    #     old_data = [zhuansu]
                    #     # print((np.array([old_data]) == np.array([zs_insert_data
                    #     #                                          ])).all())
                    #     if (np.array([old_data]) == np.array([zs_insert_data
                    #                                           ])).all():
                    #         print(jizu_num + 1, '# 转速 skip')
                    #     else:
                    #         # print("insert")
                    #         sql = f'''INSERT INTO "main"."zhuansu" ("ename", "zhuansu", "time")
                    #                 VALUES({jizu_num+1}, {','.join(zs_new_data)}, {int(recvtime)})'''
                    #         # print(sql)
                    #         cur.execute(sql)
                    # 叶片角度
                    ypjd_new_data = results[178 + jizu_num]
                    ypjd_insert_data = decimal.Decimal(ypjd_new_data).quantize(
                        decimal.Decimal('0'), rounding=decimal.ROUND_HALF_UP)
                    sql1 = f'''SELECT "ypjd" FROM "main"."yepianjiaodu" WHERE "ename"="{jizu_num+1}" ORDER BY "time" DESC LIMIT 1'''
                    res = cur.execute(sql1)
                    db_res = res.fetchall()
                    if db_res:
                        old_data = db_res[0][0]
                        # old_data = round(old_data, 1)
                        old_data = decimal.Decimal(old_data).quantize(
                            decimal.Decimal('0'),
                            rounding=decimal.ROUND_HALF_UP)
                        if old_data == ypjd_insert_data:
                            print(jizu_num + 1, '# 叶片角度 skip')
                        else:
                            sql = f'''INSERT INTO "main"."yepianjiaodu" ("ename", "ypjd", "time") VALUES({jizu_num+1}, {ypjd_new_data}, {int(recvtime)})'''
                            print(f"\033[4m{sql}\033[0m")
                            cur.execute(sql)
                # 水位流量
                swll_new_data = [results[-3], results[-2], results[-1]]
                swll_new_data1 = list(map(float, swll_new_data))
                sql1 = f'''SELECT sysw,xysw,sqsw FROM "swll" ORDER BY "time" DESC LIMIT 1'''
                # cur.execute(sql1)
                res = cur.execute(sql1)
                db_res = res.fetchall()
                if db_res:
                    sysw, xysw, sqsw = db_res[0]
                    old_data = [sysw, xysw, sqsw]
                    old_data = list(map(float, old_data))
                    if (np.array([old_data]) == np.array([swll_new_data1
                                                          ])).all():
                        print(f"\033[33m水位、流量skip\033[0m")
                    else:
                        sql = f'''INSERT INTO "swll" (sysw,xysw,sqsw,time) VALUES({','.join(swll_new_data)},{int(recvtime)})'''
                        print(f"\033[4m{sql}\033[0m")
                        cur.execute(sql)
            # 发电状态
            else:
                with open('./data/log_fd_bak1.txt', 'a') as f:
                    f.write(
                        str(int(recvtime)) + ',' + ','.join(results) + '\n')
                # results_length = len(results)
                for i in range(5):
                    dydl_byq_new_data = [
                        results[0 + i * 9],
                        results[1 + i * 9],
                        results[2 + i * 9],
                        results[3 + i * 9],
                        results[4 + i * 9],
                        results[5 + i * 9],
                        results[6 + i * 9],
                        results[7 + i * 9],
                        results[8 + i * 9],
                    ]
                    dydl_byq_insert_data = list(map(float, dydl_byq_new_data))

                    sql1 = f'''SELECT "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos" FROM "main"."dianyadianliu" WHERE "ename" LIKE "{byq_list[i]}" ORDER BY "time" DESC LIMIT 1'''
                    cur.execute(sql1)
                    res = cur.execute(sql1)
                    db_res = res.fetchall()
                    if db_res:
                        Uab, Ubc, Uca, Ia, Ib, Ic, P, Q, COS = db_res[0]
                        old_data = [Uab, Ubc, Uca, Ia, Ib, Ic, P, Q, COS]
                        if (np.array([old_data
                                      ]) == np.array([dydl_byq_insert_data
                                                      ])).all():
                            print(f"\033[33m{byq_list[i]}# 电气skip\033[0m")
                        else:
                            # print("insert")
                            dydl_byq_insert_data = list(
                                map(str, dydl_byq_insert_data))
                            sql = f'''INSERT INTO "main"."dianyadianliu" ("ename", "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos","time") VALUES("{byq_list[i]}", {','.join(dydl_byq_insert_data)}, {int(recvtime)})'''
                            print(f"\033[4m{sql}\033[0m")
                            cur.execute(sql)
                conn.commit()
                for jizu_num in range(4):
                    # 电压电流功率cos
                    dydl_new_data = [
                        results[54 + jizu_num * 9],
                        results[55 + jizu_num * 9],
                        results[56 + jizu_num * 9],
                        results[57 + jizu_num * 9],
                        results[58 + jizu_num * 9],
                        results[59 + jizu_num * 9],
                        results[60 + jizu_num * 9],
                        results[61 + jizu_num * 9],
                        results[62 + jizu_num * 9],
                        results[158 + jizu_num * 2],  # lcU
                        results[159 + jizu_num * 2]  #lcI
                    ]
                    sql = f'''INSERT INTO "main"."dianyadianliu" ("ename", "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos","lcU","lcI","time") VALUES({jizu_num + 1}, {','.join(dydl_new_data)}, {int(recvtime)})'''
                    print(f"\033[4m{sql}\033[0m")
                    cur.execute(sql)
                conn.commit()
                # 变频发电机、电动机
                for index, bp in enumerate(bpfd_list):
                    dydl_bp_data = results[174 + index * 12:174 +
                                           (index + 1) * 12]
                    dydl_bp_data.pop(-3)
                    # lc_bp_data = results[184 + index*2:184 + (index + 1)*2]
                    sql = f'''INSERT INTO "main"."dianyadianliu" ("ename", "Uab", "Ubc", "Uca", "Ia", "Ib", "Ic", "P", "Q", "cos","lcU","lcI","time") VALUES("{bp}", {','.join(dydl_bp_data)}, {int(recvtime)})'''
                    print(f"\033[4m{sql}\033[0m")
                    cur.execute(sql)
                conn.commit()
                # 变压器温度
                byq_wendu_list = results[45:54]
                byq_wendu_list.append(str(int(recvtime)))
                sql = 'INSERT INTO byqwd (zbwd,sbA,sbB,sbC,sbD,zbA,zbB,zbC,zbD,time) VALUES(?,?,?,?,?,?,?,?,?,?)'
                cur.execute(sql, byq_wendu_list)
                print(f"\033[4m{byq_wendu_list}\033[0m")
                conn.commit()
                # 供水
                gongshui_new_data = (int(recvtime), results[198], results[199],
                                     results[200], results[201], results[202])
                print(f"\033[4m{gongshui_new_data}\033[0m")
                sql = '''INSERT INTO gongshui (time,mgyl,mgwd,cswd1,cswd2,cswd3) VALUES (?,?,?,?,?,?)'''
                cur.execute(sql, gongshui_new_data)
                conn.commit()
                # 温度
                for jizu_num in range(4):
                    sql = f'''INSERT INTO "main"."wendu" ("ename", 
                    "t_U2", "t_U3", "t_V3", "t_W1", "t_W2", "t_W3", 
                    "t_Z5", "t_Z6", "t_Z7", "t_Z9", 
                    "t_X1", "t_X2", 
                    "t_Z10", "t_Z11", 
                    "t_syg", "t_xyg", 
                    "time")  VALUES(
                        {jizu_num +1}, 
                        {','.join(results[90+jizu_num*6:90+(jizu_num+1)*6])}, 
                        {','.join(results[114+jizu_num*4:114+(jizu_num+1)*4])},
                        {','.join(results[130+jizu_num*2:130+(jizu_num+1)*2])},
                        {','.join(results[138+jizu_num*2:138+(jizu_num+1)*2])},
                        {','.join(results[146+jizu_num*2:146+(jizu_num+1)*2])},
                        {int(recvtime)})'''
                    print(f"\033[4m{sql}\033[0m")
                    cur.execute(sql)
                conn.commit()
                # 水位流量
                swll_new_data = [results[-6], results[-5], results[-4]]
                swll_new_data1 = list(map(float, swll_new_data))
                sql1 = f'''SELECT sysw,xysw,sqsw FROM "swll" ORDER BY "time" DESC LIMIT 1'''
                # cur.execute(sql1)
                res = cur.execute(sql1)
                db_res = res.fetchall()
                if db_res:
                    sysw, xysw, sqsw = db_res[0]
                    old_data = [sysw, xysw, sqsw]
                    old_data = list(map(float, old_data))
                    if (np.array([old_data]) == np.array([swll_new_data1
                                                          ])).all():
                        print(f"\033[33m水位、流量skip\033[0m")
                    else:
                        sql = f'''INSERT INTO "swll" (sysw,xysw,sqsw,time) VALUES({','.join(swll_new_data)},{int(recvtime)})'''
                        print(f"\033[4m{sql}\033[0m")
                        cur.execute(sql)
    except Exception as e:
        print("save_data1错误:", e)
    conn.commit()
    cur.close()


def save_data2(recvdata, recvtime, station):
    try:
        conn = sqlite3.connect(db2_path)
        cur = conn.cursor()
        # with open('./data/lognew2.txt', 'a') as f:
        #     f.write(str(int(recvtime)) + ' ' + recvdata + '\n')
        results = re.findall(r'-{0,1}\d+\.{0,1}\d{0,3}',
                             recvdata.replace('10m', ''))

        with open('./data/log_bak2.txt', 'a') as f:
            line = str(int(recvtime)) + ',' + ','.join(results) + '\n'
            f.write(line)
        db_time = int(recvtime)
        # print(results)
        for i in range(4):
            db_name = str(i + 1)
            db_content = []
            for j in range(11):
                db_content.append(results[i + 4 * j])
            db_content.pop(-3)
            db_content = ','.join(db_content)
            sqltxt = f"""INSERT INTO dydlpqcos (time,name,Ia,Ic,Uab,Ubc,Uca,P,Q,COS,LC1,LC2) VALUES ({db_time},{db_name},{db_content})"""
            # print(f"\033[4m{sqltxt}\033[0m")
            cur.execute(sqltxt)
        for i in range(4):
            db_name = erzhan_list[i]
            db_content = []
            for j in range(8):
                db_content.append(results[i + 44 + 4 * j])
            db_content = ','.join(db_content)
            sqltxt = f"""INSERT INTO dydlpqcos (time,name,Ia,Ic,Uab,Ubc,Uca,P,Q,COS) VALUES ({db_time},"{db_name}",{db_content})"""
            # print(f"\033[4m{sqltxt}\033[0m")
            cur.execute(sqltxt)
        for i in range(4):
            db_name = str(i + 1)
            db_dq_content = []
            for j in range(11):
                db_dq_content.append(results[i + 80 + 4 * j])
            db_dq_content = ','.join(db_dq_content)
            sqltxt = f"""INSERT INTO dqcs (time,name,ypjd,zkd,bd,xjjsp,xjjcz,jzzd1,jzzd2,zs,sygyw,xygyw,jsll) VALUES ({db_time},{db_name},{db_dq_content})"""
            # print(f"\033[4m{sqltxt}\033[0m")
            cur.execute(sqltxt)
        db_zl_content = []
        for i in range(10):
            db_zl_content.append(results[i + 124])
        db_zl_content = ','.join(db_zl_content)
        sqltxt = f"""INSERT INTO zlcs (time,dc1dy,km1dy,hm1dy,dc1dl,km1dl,Uab1lu,Ubc1lu,Uca1lu,hm1dl,dc1wd) VALUES({db_time},{db_zl_content})"""
        # print(f"\033[4m{sqltxt}\033[0m")
        cur.execute(sqltxt)
        db_qzcs_content = []
        for i in range(9):
            db_qzcs_content.append(results[i + 134])
        db_qzcs_content = ','.join(db_qzcs_content)
        sqltxt = f"""INSERT INTO qzcs (time,sysw,sqsw,shsw,yc,ldsw,zbwd1,zbwd2,yxts,zll) VALUES({db_time},{db_qzcs_content})"""
        # print(f"\033[4m{sqltxt}\033[0m")
        cur.execute(sqltxt)
        for i in range(4):
            db_name = str(i + 1)
            db_wd_content = []
            for j in range(22):
                db_wd_content.append(results[i + 143 + 4 * j])
            db_wd_content = ','.join(db_wd_content)
            sqltxt = f"""INSERT INTO wd (time,name,tlw1,tlw2,tlw3,tlw4,tlw5,tlw6,sdw,syg,xdw,xyg,dz1,dz2,dz3,dz4,dz5,dz6,dz7,dz8,dz9,lc1,lc2,lc3) VALUES ({db_time},{db_name},{db_wd_content})"""
            # print(f"\033[4m{sqltxt}\033[0m")
            cur.execute(sqltxt)
        conn.commit()
        # with open('./data/check.json', 'r') as f:
        #     j = json.load(f)
        #     j[station] = 1
        # with open('./data/check.json', 'w') as f:
        #     json.dump(j, f)
        # get_main_image('刘老涧抽水二站', 2, background_color=(0, 0, 205))
        # th = Thread(target=get_main_image, args=(
        #     '二站',
        #     2,
        # ))
        # th.daemon = True
        # th.start()
        # 清空报错信息
        with open(f'./data/errormsg{station}.txt', 'w') as f:
            f.write("")
    except Exception as e:
        if "list index out of range" in str(e):
            print(f"\033[33m{e}  尝试存入一站系统\033[0m")
            recvdata = zlib.compress(bytes(recvdata, 'utf-8'))
            requests.post('http://127.0.0.1:5000/zhongkong',
                          data=recvdata + b'END')
        else:
            print(e)
    finally:
        cur.close()
        conn.close()


def save_api_data(recvdata, recvtime, station):
    '''
    1站 保存由api读取的数据10分钟 存一次
    '''


'''

INSERT INTO `data` 
(`name`,`time`,`1`,`2`,`3`,`4`,`5`,`6`,`7`,`8`,`9`,`10`,`11`,`12`,`13`,`14`,`15`,`16`,`17`,`18`,`19`,`20`,`21`,`22`,`23`,`24`,`25`,`26`,`27`,`28`,`29`,`30`,`31`,`32`,`33`,`34`,`35`,`36`,`37`,`38`,`39`,`40`,`41`,`42`,`43`,`44`,`45`,`46`,`47`,`48`,`49`,`50`,`51`,`52`,`53`,`54`,`55`,`56`,`57`,`58`,`59`,`60`,`61`,`62`) 
VALUES ('ZJX', 1, 37.25,0.0,1.0,86.0,5433.0,-1336.0,0.97,6.45,494.0,37.25,5440.0,-1530.0,0.96,23.0,199.0,174.0,0.75,0.0,0.0,0.0,37.36,0.0,0.0,1.0,6.49,6.48,6.53,0.0,-1.87,0.0,0.0,85.0,0.0,-0.76,5410.0,-1311.0,0.97,0.0,0.0,1,1,None,None,None,None,1,1,1,1,1,1,1,0,0,0,0,0,0,1,1,1,1)
'''
